package com.seabook.store.goodmgr.dao;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.util.StringUtils;
import com.seabook.store.goodmgr.domain.Category;
import com.seabook.store.goodmgr.domain.Product;

public class ProductDaoImpl extends JdbcDaoSupport implements ProductDao {

    private ProductRowMapper mapper = new ProductRowMapper();

    private GeneralProductRowMapper gmapper = new GeneralProductRowMapper();

    private final String updateSql = " update Product  set name=?, productNo=?, unitprice=?, unitpriceCur=?, categoryId=?, imgUrl=?, description=?,"
            + " totalAmount = ?, currentAmount=? " + " where productId = ?";

    private final String insertSql = " insert into Product ( "
            + " productId, productNo, name, unitprice, unitpriceCur, categoryId, imgUrl, description,totalAmount, currentAmount)"
            + " values (?,?,?,?,?,?,?,?,?,?)";

    public List<Product> getProductByCategory(int categoryId, String sortField) {
        // TODO Auto-generated method stub
        StringBuffer sb = new StringBuffer();
        sb
                .append("select p.categoryId, productId, productNo, p.name, unitprice, unitpriceCur, totalAmount, currentAmount, imgUrl from Product p, Category c ");
        sb.append(" where p.categoryId = c.id ");
        if (0 != categoryId)
            sb.append(" and (p.categoryId = ? or c.categoryId = ? ) ");
        sb.append(" order by ");
        sb.append(sortField);
        String sql = sb.toString();
        if (0 == categoryId)
            return this.getJdbcTemplate().query(sql, gmapper);
        else
            return this.getJdbcTemplate().query(sql,
                    new Object[] { categoryId, categoryId }, gmapper);
    }

    public void saveOrUpdateProduct(Product product) {
        if (product.getProductId() == 0)
            this.getJdbcTemplate().update(
                    insertSql,
                    Math.abs((int) System.currentTimeMillis()),
                    product.getProductNo(),
                    product.getName(),
                    product.getUnitPrice(),
                    product.getUnitPriceCur(),
                    product.getCategoryId(),
                    StringUtils.collectionToCommaDelimitedString(product
                            .getImgUrls()), product.getDescription(),
                    product.getAmount(), product.getCurAmount());
        else {
            this.getJdbcTemplate().update(
                    updateSql,
                    product.getName(),
                    product.getProductNo(),
                    product.getUnitPrice(),
                    product.getUnitPriceCur(),
                    product.getCategoryId(),
                    StringUtils.collectionToCommaDelimitedString(product
                            .getImgUrls()), product.getDescription(),
                    product.getAmount(), product.getCurAmount(),
                    product.getProductId());
        }
    }

    class GeneralProductRowMapper implements RowMapper<Product> {
        public Product mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Product product = new Product();
            product.setProductId(rs.getInt("productId"));
            product.setProductNo(rs.getString("productNo"));
            product.setName(rs.getString("name"));
            product.setUnitPrice(rs.getDouble("unitprice"));
            product.setUnitPriceCur(rs.getDouble("unitpriceCur"));
            product.setAmount(rs.getInt("totalAmount"));
            product.setCurAmount(rs.getInt("currentAmount"));
            // product.setAmount(rs.getInt("totalAmount"));
            // product.setCurAmount(rs.getInt("currentAmount"));
            // product.setDescription(rs.getString("description"));
            product.setImgUrls(rs.getString("imgUrl"));

            return product;
        }
    }

    class ProductRowMapper implements RowMapper<Product> {
        public Product mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Product product = new Product();
            product.setCategoryId(rs.getInt("categoryId"));
            product.setProductId(rs.getInt("productId"));
            product.setProductNo(rs.getString("productNo"));
            product.setName(rs.getString("name"));
            product.setUnitPrice(rs.getDouble("unitprice"));
            product.setUnitPriceCur(rs.getDouble("unitpriceCur"));
            product.setAmount(rs.getInt("totalAmount"));
            product.setCurAmount(rs.getInt("currentAmount"));
            product.setDescription(rs.getString("description"));
            product.setImgUrls(rs.getString("imgUrl"));
            return product;
        }
    }

    public List<Product> getProductByCategory(int categoryId) {
        return getProductByCategory(categoryId, "productId");
    }

    public Product getProductById(int productId) {
        return this.getJdbcTemplate().queryForObject(
                "select * from Product where productId = ? ",
                new Object[] { productId }, mapper);
    }
}
